from PyQt5.QtWidgets import QApplication, QMainWindow,QWidget,QTableWidgetItem,QMessageBox,QLineEdit
from PyQt5.uic import loadUiType
import pymysql
from PyQt5 import QtCore

UI1, _ =loadUiType('zhuchuankou.ui')
UI2, _ =loadUiType('zhuce.ui')
UI3, _ =loadUiType('gaimi.ui')
UI4, _ =loadUiType('guanliyuan.ui')
UI5, _ =loadUiType('xuesheng.ui')

#登录界面
class DlApp(QMainWindow,UI1):
    def __init__(self):          #构造函数方法
        super(DlApp,self).__init__()   #调用父类构造
        self.setupUi(self)        #初始化操作
        self.button()             #调用定义的按钮函数

#槽函数
    def button(self):
        self.pushButton_zc.clicked.connect(self.zhuce)  #注册按钮与注册界面连接
        self.pushButton_dl.clicked.connect(self.denglu) #登录按钮与主界面连接
        self.pushButton_gm.clicked.connect(self.gaimi)  #改密按钮与改密界面连接

#登录跳转到注册页面
    def zhuce(self):
        self.a=ZcApp()
        self.close()
        self.a.show()    # 注册界面打开
    def gaimi(self):
        self.z=GmApp()
        self.z.show()    #保留登陆界面同时打开改密界面
#登录界面跳转到主界面
    def denglu(self):
        a = self.lineEdit_name.text()
        b = self.lineEdit_2.text()
        if self.radioButton_2.isChecked() == True:  # isChecked()来查询radioButton是否被选中
            if (a == "" or b == ""):
                print(QMessageBox.warning(self, "警告", "输入不可为空，请重新输入", QMessageBox.Yes, QMessageBox.Yes))
                return
            db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
            cur = db.cursor()
            sql1 = "SELECT * FROM user WHERE (id=%s) "
            cur.execute(sql1, (a))
            db.commit()  # 提交
            cur.close()
            db.close()
            data = cur.fetchone()
            # 如果用户不存在
            if not data:
                print(QMessageBox.warning(self, "警告", "管理员账号不存在，请重新输入或注册", QMessageBox.Yes, QMessageBox.Yes))
                self.lineEdit_name.clear()
                self.lineEdit_2.clear()
                return
            else:
                self.c = MainApp()
                self.close()     #当前窗口关闭
                self.c.show()     #主窗口打开

        if self.radioButton_xs1.isChecked() == True:  # isChecked()来查询radioButton是否被选中
            if (a == "" or b == ""):
                print(QMessageBox.warning(self, "警告", "输入不可为空，请重新输入", QMessageBox.Yes, QMessageBox.Yes))
                return
            db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
            cur = db.cursor()
            sql1 = "SELECT * FROM user1 WHERE (id=%s) "
            cur.execute(sql1, (a))
            db.commit()  # 提交
            cur.close()
            db.close()
            data = cur.fetchone()
            # 如果用户不存在
            if not data:
                print(QMessageBox.warning(self, "警告", "学生账号不存在，请重新输入或注册", QMessageBox.Yes, QMessageBox.Yes))
                self.lineEdit_name.clear()
                self.lineEdit_2.clear()
                return
            else:                 #如果存在数据，则执行以下功能
                self.b = MainApp1()
                self.close()
                self.b.show()



#注册界面
class ZcApp(QMainWindow,UI2):
    def __init__(self):
        super(ZcApp,self).__init__()   #调用父类构造
        self.setupUi(self)
        self.button()

    def button(self):
        self.pushButton_fh2.clicked.connect(self.fanhui)  # 从注册界面返回登录界面
        self.pushButton_zc2.clicked.connect(self.zhuceusers)      # 注册按钮与注册用户的函数相连接


    def fanhui(self):      #返回登录界面的函数
        self.a1=DlApp()
        self.close()
        self.a1.show()

    def zhuceusers(self):   # 注册函数
        if self.radioButton_2.isChecked() == True:  # isChecked()来查询radioButton是否被选中
            db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
            cur = db.cursor()           #创建游标
            sql = "insert into user(id,pwd) values(%s,%s)"  #sql语句
            username = self.lineEdit_3.text()  # 实现从指定界面输入框获取指定输入信息
            userpwd = self.lineEdit.text()
            userpwd1 = self.lineEdit_2.text()
            if userpwd == userpwd1:     # 如果密码和确认密码相同，则执行以下代码
                cur.execute(sql, (username,userpwd))  #执行sql语句
                db.commit()                           # 提交
                cur.close()
                db.close()# 关闭数据库
                QMessageBox.information(self, "提醒", "您已成功注册管理员账号!", QMessageBox.Yes, QMessageBox.Yes)

            else:
                QMessageBox.warning(self, "警告", "出错了！，请重新输入", QMessageBox.Yes, QMessageBox.Yes)

        if self.radioButton_xs2.isChecked()==True:   # isChecked()来查询radioButton是否被选中
            db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
            cur = db.cursor()           #创建游标
            sql = "insert into user1(id,pwd) values(%s,%s)"  #sql语句
            username = self.lineEdit_3.text()  # 实现从指定界面输入框获取指定输入信息
            userpwd = self.lineEdit.text()
            userpwd1 = self.lineEdit_2.text()
            if userpwd == userpwd1:     # 如果密码和确认密码相同，则执行以下代码
                cur.execute(sql, (username,userpwd))  #执行sql语句
                db.commit()                           # 提交
                cur.close()
                db.close()# 关闭数据库
                QMessageBox.information(self, "提醒", "您已成功注册学生账号!", QMessageBox.Yes, QMessageBox.Yes)

            else:
                QMessageBox.warning(self, "警告", "出错了！，请重新输入", QMessageBox.Yes, QMessageBox.Yes)



#图书馆界面
class MainApp(QMainWindow,UI4):
    def __init__(self):
        super(MainApp,self).__init__()   #调用父类构造
        self.setupUi(self)
        self.button()    # 调用按钮
        self.show_bookinfo()   # 显示书籍信息
        self.show_jhinfo()   # 显示借还息

#按钮与函数连接部分
    def button(self):
        self.pushButton_fh.clicked.connect(self.fanhui)   # 返回登录界面
        self.addqx_info.clicked.connect(self.add_bookinfo)   # 图书馆管理里面的增加按钮连接
        self.pushButton_select.clicked.connect(self.select_bookinfo)   # 查询
        self.pushButton_select_2.clicked.connect(self.book_detele_xiugai)  # 搜索用来修改删除
        self.pushButtonxg1.clicked.connect(self.xiugai_book_info)  # 修改
        self.pushButton_shangchu.clicked.connect(self.delete_bookinfo)  # 删除

 #返回登录界面自定义函数
    def  fanhui(self):
        self.a4 = DlApp()  #实例变量
        self.close()
        self.a4.show()

#增加书籍信息
    def add_bookinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor() #获取游标
        sql = "insert into book_info(id,name,wz,host,js) values(%s,%s,%s,%s,%s)"
        QMessageBox.information(self, "提醒", "添加成功", QMessageBox.Yes, QMessageBox.Yes)
        a = self.book_id.text()    #输入框获取指定输入信息
        b= self.book_name.text()
        c = self.book_wz.text()
        d= self.book_host.text()
        e = self.book_js.text()
        cur.execute(sql, (a,b,c,d,e))  #执行
        db.commit()    #提交
        db.close()
        cur.close()
        self.show_bookinfo()   # 同步展示
        self.show_jhinfo()
#显示书籍信息
    def show_bookinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor()
        sql = "select * from book_info"
        cur.execute(sql)
        data = cur.fetchall()    # 获取查询到的数据，以二维元组的存储，读取使用data[i][j] 下标定位
        print(data)
        if data:
            self.tableWidget_book.setRowCount(0)
            self.tableWidget_book.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget_book.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget_book.rowCount()
                self.tableWidget_book.insertRow(row_position)
#查询书籍信息
    def select_bookinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor()
        sql = "select * from book_info where id=%s"
        id = self.book_id_select.text()
        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.book_id_2.setText(str(data[0]))
            self.book_name_2.setText(data[1])
            self.book_wz_2.setText(str(data[2]))
            self.book_host_2.setText(str(data[3]))
            self.textEdit.setText(data[4])

#查询书籍信息用来修改和删除
    def book_detele_xiugai(self):
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor()
        sql = "select * from book_info where id=%s"
        id = self.bookid3.text()

        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.groupBox_14.setEnabled(True)
            self.book_id_3.setText(str(data[0]))
            self.book_name_3.setText(data[1])
            self.book_wz_3.setText(str(data[2]))
            self.book_host_3.setText(str(data[3]))
            self.textEdit_2.setText(data[4])
#修改书籍信息
    def xiugai_book_info(self):
        id = self.bookid3.text()
        a=self.book_id_3.text()
        b=self.book_name_3.text()
        c=self.book_wz_3.text()
        d=self.book_host_3.text()
        e = self.textEdit_2.toPlainText()
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor()
        sql = "update book_info set id=%s,name=%s,wz=%s,host=%s,js=%s where id=%s"
        QMessageBox.information(self, "提醒", "修改成功", QMessageBox.Yes, QMessageBox.Yes)
        cur.execute(sql, (a,b,c,d,e,id))
        db.commit()
        db.close()
        cur.close()
        self.show_bookinfo()   # 同步显示
        self.show_jhinfo()
#删除书籍信息
    def delete_bookinfo(self):
        id = self.bookid3.text()
        warning = QMessageBox.warning(self, "删除书籍信息", "您确定删除吗？", QMessageBox.Yes | QMessageBox.No)
        if warning == QMessageBox.Yes:
            db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
            cur = db.cursor()
            sql = "delete from book_info where id=%s"
            QMessageBox.information(self, "提醒", "删除成功！请等待数据更新", QMessageBox.Yes, QMessageBox.Yes)
            cur.execute(sql, (id))
            db.commit()
            db.close()
            cur.close()
            self.show_bookinfo()
            self.show_jhinfo()

#显示借还信息
    def show_jhinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor()
        sql = "select * from book_info"
        cur.execute(sql)
        data = cur.fetchall()
        print(data)
        if data:
            self.tableWidget_book_2.setRowCount(0)
            self.tableWidget_book_2.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget_book_2.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget_book_2.rowCount()
                self.tableWidget_book_2.insertRow(row_position)



#学生登录主界面
class MainApp1(QMainWindow,UI5):
    def __init__(self):
        super(MainApp1,self).__init__()   #调用父类构造
        self.setupUi(self)
        self.button()
        self.show_bookinfo()


    def button(self):
        self.pushButton_select_3.clicked.connect(self.select_bookinfo)
        self.pushButton8_2.clicked.connect(self.xiugai_jhinfo)
        self.pushButton7_2.clicked.connect(self.select_jieinfo)
        self.pushButton_fh.clicked.connect(self.fanhui)


    def fanhui(self):
        self.a1=DlApp()
        self.close()
        self.a1.show()
#显示书籍信息
    def show_bookinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor()
        sql = "select id,name,wz,host,js,zt from book_info"
        cur.execute(sql)
        data = cur.fetchall()
        print(data)
        if data:
            self.tableWidget_book_2.setRowCount(0)
            self.tableWidget_book_2.insertRow(0)
            for row, form in enumerate(data):
                for column, item in enumerate(form):
                    self.tableWidget_book_2.setItem(row, column, QTableWidgetItem(str(item)))
                    column += 1
                row_position = self.tableWidget_book_2.rowCount()
                self.tableWidget_book_2.insertRow(row_position)

#查询书籍信息
    def select_bookinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor()
        sql = "select id,name,wz,host,js,zt from book_info where id=%s"
        id = self.book_id_select_2.text()
        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.book_id_4.setText(str(data[0]))
            self.book_name_4.setText(data[1])
            self.book_wz_4.setText(str(data[2]))
            self.book_host_4.setText(str(data[3]))
            self.textEdit_3.setText(str(data[4]))
            self.lineEdit.setText(str(data[5]))


#查询书籍信息来修改
    def select_jieinfo(self):
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
        cur = db.cursor()
        sql = "select * from book_info where id=%s"
        id = self.id6_2.text()
        cur.execute(sql, (id))
        data = cur.fetchone()
        if data:
            self.book_id_5.setText(str(data[0]))
            self.book_name_5.setText(data[1])
            self.book_wz_5.setText(str(data[2]))
            self.book_host_5.setText(str(data[3]))
            self.textEdit_4.setText(str(data[4]))

#学生进行借还书操作
    def xiugai_jhinfo(self):
        id = self.id6_2.text()
        e = self.lineEdit4_6.text()
        g = self.comboBox_2.currentText()
        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg",charset="utf8")
        cur = db.cursor()
        sql = "update book_info set stu_name=%s,time =now(),zt=%s where id=%s"
        QMessageBox.information(self, "提醒", "书籍借还成功！请查询状态是否正常", QMessageBox.Yes, QMessageBox.Yes)
        cur.execute(sql, (e,g, id))
        db.commit()
        db.close()
        cur.close()
        self.show_bookinfo()



#修改密码
class GmApp(QWidget,UI3):
    def __init__(self):          #构造函数方法
        super(GmApp,self).__init__()   #调用父类构造
        self.setupUi(self)        #初始化操作
        self.button()             #调用定义的按钮函数

# 自定义按钮连接槽函数
    def button(self):
        self.pushButton_fh3.clicked.connect(self.fh)
        self.pushButton_qr.clicked.connect(self.user_login)

    def fh(self):
            self.a=DlApp()   # 把注册界面zcapp()赋值给a
            self.close()     # 当前界面关闭
            self.a.show()    # 注册界面打开

    def user_login(self):
        a = self.lineEdit.text()
        b = self.lineEdit_2.text()
        c = self.lineEdit_3.text()
        d = self.lineEdit_4.text()
        if self.radioButton_gly3.isChecked() == True:
            if (a== "" or b == "" or c == "" or d == ""):
                print(QMessageBox.warning(self, "警告", "输入不可为空，请重新输入", QMessageBox.Yes, QMessageBox.Yes))
                return
            db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
            cur = db.cursor()
            sql1 = "SELECT * FROM user WHERE (id=%s) "
            cur.execute(sql1,(a))
            db.commit()  # 提交
            cur.close()
            db.close()
            data = cur.fetchone()
            # 如果用户不存在
            if not data:
                print(QMessageBox.warning(self, "警告", "该账号不存在，请重新输入", QMessageBox.Yes, QMessageBox.Yes))
                self.lineEdit.clear()
                return
            else:
                db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
                cur = db.cursor()

                sql2 = "SELECT * FROM user WHERE id=%s and pwd=%s"
                cur.execute(sql2,(a,b))
                cur.close()
                db.close()
                data1 = cur.fetchone()
                if not data1:
                    print(QMessageBox.warning(self, "警告", "原密码输入错误,请重新输入", QMessageBox.Yes, QMessageBox.Yes))
                    self.lineEdit_2.clear()
                else:
                    # 密码与确认密码不同
                    if (c != d):
                        print(QMessageBox.warning(self, "警告", "两次输入密码不同,请确认输入", QMessageBox.Yes, QMessageBox.Yes))
                        self.lineEdit_3.clear()
                        self.lineEdit_4.clear()
                        return
                    else:
                        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
                        cur = db.cursor()
                        sql5 = "UPDATE user SET pwd=%s WHERE id=%s"
                        cur.execute(sql5,(d,a))
                        db.commit()
                        cur.close()
                        db.close()
                        QMessageBox.information(self, "提醒", "修改密码成功，请登录系统!", QMessageBox.Yes, QMessageBox.Yes)
                        return

        if self.radioButton_xs3.isChecked() == True:
            if (a == "" or b == "" or c == "" or d == ""):
                print(QMessageBox.warning(self, "警告", "输入不可为空，请重新输入", QMessageBox.Yes, QMessageBox.Yes))
                return
            db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
            cur = db.cursor()
            sql3 = "SELECT * FROM user1 WHERE id=%s"
            cur.execute(sql3, (a))
            db.commit()  # 提交
            cur.close()
            db.close()
            data4 = cur.fetchone()
            # 如果用户不存在
            if not data4:
                print(QMessageBox.warning(self, "警告", "该账号不存在，请重新输入", QMessageBox.Yes, QMessageBox.Yes))
                self.lineEdit.clear()
            else:
                db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
                cur = db.cursor()

                sql2 = "SELECT * FROM user1 WHERE id=%s and pwd=%s"
                cur.execute(sql2, (a, b))
                cur.close()
                db.close()
                data5 = cur.fetchone()
                if not data5:
                    print(QMessageBox.warning(self, "警告", "原密码输入错误,请重新输入", QMessageBox.Yes, QMessageBox.Yes))
                    self.lineEdit_2.clear()

                else:
                    # 密码与确认密码不同
                    if (c != d):
                        print(QMessageBox.warning(self, "警告", "两次输入密码不同,请确认输入", QMessageBox.Yes, QMessageBox.Yes))
                        self.lineEdit_3.clear()
                        self.lineEdit_4.clear()

                    else:
                        db = pymysql.connect(host="localhost", user="root", passwd="root", database="tsg", charset="utf8")
                        cur = db.cursor()
                        sql5 = "UPDATE user1 SET pwd=%s WHERE id=%s"
                        cur.execute(sql5, (d, a))
                        db.commit()
                        cur.close()
                        db.close()
                        QMessageBox.information(self, "提醒", "修改密码成功，请登录系统!", QMessageBox.Yes, QMessageBox.Yes)


def main():
    app = QApplication([])

    window = DlApp()         # 创建主窗口
    window.show()              # 显示窗体
    app.exec_()                #程序关闭时退出进程

if __name__ == '__main__':

    main()